home *** CD-ROM | disk | FTP | other *** search
/ Software Vault: The Gold Collection / Software Vault - The Gold Collection (American Databankers) (1993).ISO / cdr11 / powerb5.zip / P5SPR003.TIP < prev    next >
Text File  |  1993-06-01  |  5KB  |  111 lines
  1. Loans with variable interest rates can present problems if
  2. you're trying to create a table of payments in a
  3. spreadsheet. Many books show how to calculate payments for
  4. fixed-rate loans using 1-2-3's @PMT() function, but I looked
  5. through more than a dozen books on spreadsheets and basic
  6. accounting, and I couldn't find one technique for
  7. calculating variable-rate payments. So I developed my own.
  8.  
  9. The @PMT() function found in most spreadsheets takes the
  10. form @PMT(principal, interest, term), where principal is the
  11. amount of the loan, interest is the interest rate per period
  12. (usually per month or year), and term is the number of
  13. payment periods. But @PMT() doesn't take variable interest
  14. rates into account; it assumes that the payments remain
  15. constant. My solution is to use @PMT() to calculate
  16. different payments each month. This is the equivalent of
  17. assuming that the loan starts over again each month, with
  18. one less month in the term.
  19.  
  20. Here's a sample spreadsheet for a six-month loan [FIGURE 1].
  21. The first two rows include general information about the
  22. loan. Starting in row 6, column A lists each payment's due
  23. date, column B the number of payments remaining, and column
  24. C the interest rate. Columns D through G use assorted
  25. formulas to calculate each month's total payment, the
  26. interest and principal portions of that payment, and the
  27. remaining principal balance.
  28.  
  29.         A        B        C        D         E        F         G
  30.  
  31. 1  Term of loan:   6 months              Amount of loan     10,000.00
  32. 2  Date of loan:     09-Sep-91
  33. 3
  34. 4  Payment      Pmts  Interest   Monthly  Paid as   Paid as   Balance
  35. 5  due on       left      rate   payment interest principal remaining
  36. 6     09-Oct-91    6     10.00% 1,715.61    83.33  1,632.28  8,367.72
  37. 7     09-Nov-91    5     10.00% 1,715.61    69.73  1,645.88  6,721.84
  38. 8     09-Dec-91    4     10.00% 1,715.61    56.02  1,659.60  5,062.24
  39. 9     09-Jan-92    3     17.00% 1,735.45    71.72  1,663.73  3,398.51
  40. 10    09-Feb-92    2     17.00% 1,735.45    48.15  1,687.30  1,711.21
  41. 11    09-Mar-92    1     17.00% 1,735.45    24.24  1,711.21      0.00
  42.  
  43. This amortization table for a variable-rate loan was created
  44. using the @PMT function. The trick: the formula is applied
  45. as if the loan starts over each month.
  46.  
  47. If you create this table yourself, be sure to fill in the
  48. correct formulas [See table, below]. With the exception of
  49. the interest rate, rows 8 through 11 of the worksheet
  50. contain the same formulas as row 7; once you have written
  51. the first seven rows, use /Copy to copy A7..G7 to A8..G11.
  52. The key to this worksheet is the series of @PMT() formulas
  53. in column D. Each one reads a different balance and a
  54. different interest rate, producing dynamic (and possibly
  55. depressing) monthly payments.
  56.  
  57. C2:  @DATEVALUE("3/9/92")
  58. A6:  +C2+@CHOOSE(@MONTH(C2)-1,31,@IF(@MOD(@YEAR(C2),4)=0,29,28),31,30,31,30,31,31,30,31,30,31)
  59. D6:  @PMT(G1,C6/12,B6)
  60. E6:  +C6/12*G1
  61. F6:  +D6-E6
  62. G6:  +G1-F6
  63. A7:  +A6+@CHOOSE(@MONTH(A6)-1,31,@IF(@MOD(@YEAR(A6),4)=0,29,28),31,30,31,30,31,31,30,31,30,31)
  64. B7:  +B6-1
  65. C7:  +C6
  66. D7:  @PMT(G6,C7/12,B7)
  67. E7:  +C7/12*G6
  68. F7:  +D7-E7
  69. G7:  @ROUND(G6-F7,2)
  70.  
  71. These formulas were used to create the spreadsheet above.
  72. Rows 8 through 11 are created by copying row 7, then
  73. changing the interest rates as required.
  74.  
  75. As long as the interest rates remain the same, so do the
  76. payments. But change the interest rate for any one month, as
  77. we did for July (the previously mentioned exception to rows
  78. 8 through 11 being identical to row 7), and everything
  79. changes from that month on. Column A shows a useful formula
  80. for listing dates that always land on the same day of the
  81. month. Basically, each formula adds 28, 29, 30, or 31 days
  82. to the date in another cell, usually the one above it. You
  83. may find it necessary to add @ROUND() functions to certain
  84. formulas. This is especially true where a formula might
  85. return 0.00. 1-2-3 may return a confusing -0.00 or 0.01; in
  86. a 30-year mortgage or a large loan, the odd penny difference
  87. can usually be ignored.
  88.  
  89. Before committing funds or signing a legal document, check
  90. with your bank or financial institution to see how they
  91. figure their variable-rate loans. Their figures and yours
  92. will probably differ slightly. One reason: Your bank will
  93. adjust your repayment schedule if you choose to make the
  94. first payment at a time other than exactly one month after
  95. the loan date.
  96.  
  97. James Kennedy,
  98. St. Augustine, Florida
  99.  
  100. Editor's Note: You don't have to enter the sample
  101. spreadsheet to try it out, because we've done it for you.
  102. Look for the file P5SPR\INTEREST.WK1 on your PowerBase *.*
  103. disk.
  104.  
  105. Title: Waxing and Waning Interest
  106. Category: SPR
  107. Issue Date: April, 1992
  108. Editor: Brett Glass
  109. Supplementary Files: P5SPR\INTEREST.WK1
  110. Filename: P5SPR003.TIP
  111.